set more off
clear all

forval dec=1900(10)1970 {
	forval year=0(1)9 {
		local year_full=`dec'+`year'
		if `dec'<=1950 {
			import excel "pe-11-`dec's.xls", sheet("`year_full'") cellrange(A8:B85) clear
		}
		if `dec'>1950 {
			import excel "pe-11-`dec's.xls", sheet("`year_full'") cellrange(A7:B85) clear
		}
		qui drop if _n==2
		sxpose, force clear
		qui drop if _n==1
		qui destring *, replace
		rename _var1 population
		egen population_below_14=rowtotal(_var2-_var15)
		egen population_below_20=rowtotal(_var2-_var21)
		gen population_above_14=population-population_below_14
		gen population_above_20=population-population_below_20
		keep population population_above_14 population_above_20
		qui replace population=population/1000
		qui replace population_above_14=population_above_14/1000
		qui replace population_above_20=population_above_20/1000
		gen year=`year_full'
		order year population*
		save data_`year_full', replace
	}
}

clear all

forval year=1900(1)1979 {
	append using data_`year'
	erase data_`year'.dta
}
save population, replace
